﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using DigDes.DSchool.SUPS.DataAccess.Interface;

namespace DigDes.DSchool.SUPS.DataAccess.Database
{
    [System.ComponentModel.DataObject]
    public class DataRdr_RailwayDB
    {
        public static List<RWLwithID> GetTable(int startRowIndex, int maximumRows)
        {
            maximumRows = startRowIndex + 10;
            {
                var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
                using (var sqlConn = new SqlConnection(connectionString))
                {
                    sqlConn.Open();
                    var sqlCmd = sqlConn.CreateCommand();
                    sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    sqlCmd.CommandText = "Select10RowsRailwayDB";
                    sqlCmd.Parameters.AddWithValue("@startRowIndex", startRowIndex);
                    sqlCmd.Parameters.AddWithValue("@maximumRows", maximumRows);
                    var reader = sqlCmd.ExecuteReader();
                    var l = new List<RWLwithID>();
                    while (reader.Read())
                    {
                        l.Add(GetRow(reader));
                    }
                    return l;
                }
            }
        }

        public int CountRailwayDB(int startRowIndex, int maximumRows)
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
            using (var sqlConn = new SqlConnection(connectionString))
            {
                sqlConn.Open();
                var sqlCmd = sqlConn.CreateCommand();
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                sqlCmd.CommandText = "CountRailwayDB";
                sqlCmd.ExecuteNonQuery();
                int count = System.Convert.ToInt32(sqlCmd.ExecuteScalar());
                sqlConn.Close();
                return count;
            }
        }

        private static RWLwithID GetRow(SqlDataReader reader)
        {
            var res = new RWLwithID();
            res.WagonNumber = reader.GetString(0);
            res.Weight = reader.GetString(1);
            res.CargoCode = reader.GetString(2);
            res.CodeOfConsignee = reader.GetString(3);
            res.TypeOfWagon = reader.GetString(4);
            res.FirstStation = reader.GetString(5);
            res.CodeOfOperation = reader.GetString(6);
            res.NumberOfTrain = reader.GetString(7);
            res.DateOfOperation = reader.GetString(8);
            res.YearOfOperation = reader.GetString(9);
            res.TimeOfOperation = reader.GetString(10);
            res.StationAchievementsOperations = reader.GetString(11);
            res.WayOfDelivery = reader.GetString(12);
            res.WayOfReception = reader.GetString(13);
            res.IndexOfTrain = reader.GetString(14);
            res.Destination = reader.GetString(15);
            try
            {
                res.Cargo_ID = reader.GetInt32(16);
            }
            catch
            {
                return res;
            }
            return res;
        }

        private static readonly string ConnectionStringKey = "SUPSDB";

        public class RWLwithID : RailwayLoader
        {
            public int Cargo_ID { get; set; }
        }
    }
}

